package com.elitel;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.InputStreamResource;
import org.springframework.core.io.support.EncodedResource;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.util.FileCopyUtils;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

public class InitSQL {
  private static final Logger logger = LoggerFactory.getLogger(InitSQL.class);

  public void getConnection(ConfigurableApplicationContext context) {

    java.sql.Connection conn = null;
    try {
      // 从properties获取数据库驱动，url，用户名，密码
      Class.forName(context.getEnvironment().getProperty("spring.db.driver-class-name"));
      String url = context.getEnvironment().getProperty("spring.db.url");
      String user = context.getEnvironment().getProperty("spring.db.username");
      String password = context.getEnvironment().getProperty("spring.db.password");
      try {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        // 获取sql脚本的路径
        /*  File path = new File(ResourceUtils.getURL("classpath:").getPath());
        logger.info("------------------"+path+"数据！-------------------");
        File upload = new File(path.getAbsolutePath(),"sql/base.sql");
        logger.info("------------------"+upload+"数据！-------------------");*/
        String data = "";
        ClassPathResource classPathResource = new ClassPathResource("sql/base.sql");
        byte[] bdata = FileCopyUtils.copyToByteArray(classPathResource.getInputStream());
        data = new String(bdata, StandardCharsets.UTF_8);
        // 读取sql脚本文件里的内容
        // RandomAccessFile read = new RandomAccessFile(upload, "rw");
        // byte[] readBytes = new byte[(int) read.length()];
        // read.read(readBytes);
        // String result = new String(readBytes);
        // 建立连接
        conn = java.sql.DriverManager.getConnection(url, user, password);
        conn.setAutoCommit(false);
        String sql =
            "select tn from (select `table_name` as tn from information_schema.TABLES t where t.TABLE_SCHEMA='base_frame') a where a.tn='sys_config'";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        // 更新版本
        if (rs.next()) {
          while (rs.next()) {
            String string = rs.getString(1);
            System.out.println("+++++" + string + "+++++");
          }
          String selectVersion = "select VALUE from sys_config where KEY_ID='base_frame'";
          ps = conn.prepareStatement(selectVersion);
          rs = ps.executeQuery();
          String version = "";
          while (rs.next()) {
            // 获取数据库sysconfig表里的版本号
            version = rs.getString(1);
          }
          // 获取与sys_config配置表中相对应的版本号的索引位置
          int versionIndex = data.indexOf(version);
          if (versionIndex != -1) {
            // 截取版本号下面的sql语句
            String sqlVersion =
                !version.equals("") ? data.substring(versionIndex + version.length()) : "";
            if (!sqlVersion.isEmpty()) {
              /* String rootDirectory = System.getProperty("user.dir");
              //创建临时文件
              File tempFile = new File(rootDirectory+"/tempSql/temp.sql");
              tempFile.delete();
              tempFile.createNewFile();
              //将截取的sql语句写入临时文件
              RandomAccessFile writer = new RandomAccessFile(tempFile, "rw");
              writer.write(sqlVersion.getBytes());*/
              InputStream inputStream = new ByteArrayInputStream(sqlVersion.getBytes("UTF-8"));
              InputStreamResource rc = new InputStreamResource(inputStream);
              // FileSystemResource rc = new FileSystemResource(tempFile);
              EncodedResource er = new EncodedResource(rc, "UTF-8");
              // 执行临时sql脚本
              try {
                ScriptUtils.executeSqlScript(conn, er);
                conn.commit();
                Date date = new Date();
                String updateDate = simpleDateFormat.format(date);
                int versionNo = data.lastIndexOf("version");
                String updateVersionNo = data.substring(versionNo).trim();
                // 更新sys_config配置表的版本号以及修改时间
                String updateVersion =
                    "update sys_config set value= ? ,updatedate= ? where key_id= ?";
                ps = conn.prepareStatement(updateVersion);
                ps.setString(1, updateVersionNo);
                ps.setString(2, updateDate);
                ps.setString(3, "base_frame");
                int row = ps.executeUpdate();
                if (row > 0) {
                  logger.info(
                      "------------------" + updateVersionNo + "数据更新成功！-------------------");
                  conn.commit();
                } else {
                  logger.error(
                      "------------------" + updateVersionNo + "数据更新失败！-------------------");
                  conn.rollback();
                }
              } catch (Exception e) {

                logger.error("脚本执行异常");
                conn.rollback();
                logger.error(e.getMessage(), e);
              }

              inputStream.close();
            } else {
              logger.info("--------------------当前版本没有更新！-------------------");
            }
          }
          // read.close();
        } else { // 初始化版本
          int versionNo = data.lastIndexOf("version");
          String firstVersion = data.substring(versionNo).trim();
          // FileSystemResource  rc = new FileSystemResource(upload);
          InputStream inputStream = new ByteArrayInputStream(data.getBytes("UTF-8"));
          InputStreamResource rc = new InputStreamResource(inputStream);
          EncodedResource er = new EncodedResource(rc, "UTF-8");
          try {
            ScriptUtils.executeSqlScript(conn, er);
            conn.commit();
            Statement st = conn.createStatement();
            String checkServices =
                "select cn from (select COLUMN_NAME as cn from information_schema.COLUMNS "
                    + "where table_name = 'base_services' and table_schema = 'base_frame')a where a.cn = 'SORT'";
            ResultSet resultSet = st.executeQuery(checkServices);
            if (!resultSet.next()) {
              String addCol = "alter table base_services add SORT int";
              st.execute(addCol);
            }
            st.addBatch(
                "replace INTO `base_services` (`GUID`, `SERVICENAME`, `SERVICEURL`, `LINKTABLE`, `REMARK`, `PAGEURL`, `SERVICETYPE`, `SORT`) VALUES ('docSearchServer', '文档检索服务', '/business/docSearchServer/docTextSearch', 'SYS_DOCUMENT', '文档检索服务', 'docSearchService/index', NULL, 2)");
            st.addBatch(
                "replace INTO `base_services` (`GUID`, `SERVICENAME`, `SERVICEURL`, `LINKTABLE`, `REMARK`, `PAGEURL`, `SERVICETYPE`, `SORT`) VALUES ('executeSqlServer', '脚本查询服务', '/business/multipleServer/multipleTableServer', 'CFG_EXTSQL', '脚本服务', 'dataServices/index', NULL, 8)");
            st.addBatch(
                "replace INTO `base_services` (`GUID`, `SERVICENAME`, `SERVICEURL`, `LINKTABLE`, `REMARK`, `PAGEURL`, `SERVICETYPE`, `SORT`) VALUES ('masterConfigServer', '主从表查询服务', '/business/extMasterServer/queryMasterTableList', 'CFG_EXTMASTER', '主从表服务', 'masterDetail/index', NULL, 5)");
            st.addBatch(
                "replace INTO `base_services` (`GUID`, `SERVICENAME`, `SERVICEURL`, `LINKTABLE`, `REMARK`, `PAGEURL`, `SERVICETYPE`, `SORT`) VALUES ('moreTableServer', '多表跨库查询服务', NULL, 'CFG_MOREJOIN', '多表跨库查询服务', 'morejoinService/index', NULL, 10)");
            st.addBatch(
                "replace INTO `base_services` (`GUID`, `SERVICENAME`, `SERVICEURL`, `LINKTABLE`, `REMARK`, `PAGEURL`, `SERVICETYPE`, `SORT`) VALUES ('reportServer', '报表查询服务', '/business/singleReportServer/singleReportShow', 'CFG_EXTREP', '报表服务', 'reportServer/index', NULL, 3)");
            st.addBatch(
                "replace INTO `base_services` (`GUID`, `SERVICENAME`, `SERVICEURL`, `LINKTABLE`, `REMARK`, `PAGEURL`, `SERVICETYPE`, `SORT`) VALUES ('searchServer', '数据检索服务', '/business/searchServer/fullTextSearch', 'CFG_EXTSEARCH', '数据检索服务', 'searchService/index', NULL, 4)");
            st.addBatch(
                "replace INTO `base_services` (`GUID`, `SERVICENAME`, `SERVICEURL`, `LINKTABLE`, `REMARK`, `PAGEURL`, `SERVICETYPE`, `SORT`) VALUES ('singleTableServer', '单表查询服务', '/business/singleServer/querySingleTB', 'CFG_EXTEND', '单表服务', 'singleTableService/index', NULL, 1)");
            st.addBatch(
                "replace INTO `base_services` (`GUID`, `SERVICENAME`, `SERVICEURL`, `LINKTABLE`, `REMARK`, `PAGEURL`, `SERVICETYPE`, `SORT`) VALUES ('staticServer', '统计查询服务', '/business/censusTab/getCensusTableing', 'CFG_EXTSTA', '统计服务', 'staticService/index', NULL, 6)");
            st.addBatch(
                "replace INTO `base_services` (`GUID`, `SERVICENAME`, `SERVICEURL`, `LINKTABLE`, `REMARK`, `PAGEURL`, `SERVICETYPE`, `SORT`) VALUES ('tableEditServer', '单表编辑服务', '/business/singleEditServer/executeEdit', 'CFG_EDIT', '单表编辑服务', 'tableEditService/index', NULL, 9)");
            st.addBatch(
                "replace INTO `base_services` (`GUID`, `SERVICENAME`, `SERVICEURL`, `LINKTABLE`, `REMARK`, `PAGEURL`, `SERVICETYPE`, `SORT`) VALUES ('treeconfigServer', '资源树查询服务', '/business/execute/selectTreeConfig', 'CFG_EXTTREE', '资源树服务', 'treeConfigure/index', NULL, 7)");
            st.executeBatch();
            st.close();
            Date date = new Date();
            String createDate = simpleDateFormat.format(date);
            String sqlVersion = "insert into sys_config values(?,?,?,?)";
            ps = conn.prepareStatement(sqlVersion);
            ps.setString(1, "base_frame");
            ps.setString(2, firstVersion);
            ps.setString(3, createDate);
            ps.setString(4, null);
            int row = ps.executeUpdate();
            if (row > 0) {
              logger.info(
                  "------------------" + firstVersion + "版本数据库初始化成功！-----------------------");
              conn.commit();
            }
          } catch (Exception e) {
            logger.error("初始化脚本异常！");
            conn.rollback();
            logger.error(e.getMessage(), e);
          }
        }
        ps.close();

      } catch (Exception e) {
        logger.error(e.getMessage(), e);
      }
    } catch (ClassNotFoundException e) {
      logger.error(e.getMessage(), e);

    } finally {
      if (conn != null) {
        try {
          conn.close();
        } catch (Exception e) {
          logger.error(e.getMessage(), e);
        }
      }
    }
  }
}
